﻿Imports System.Data.SqlServerCe
Imports util
Imports System.Data.OleDb
Imports System.Text

Public Class PersonalService

    'Private clave As String = "enahouser"
    Private clave As String = "enaho2009"

    Private con As SqlCeConnection

    Public archivoSDF As String

    '"usuario nchar(20)," & _
    '"clave nchar(8)," & _
    Dim sqlCreate As String = "CREATE TABLE Personal(" & _
                                "dpto nchar(2)," & _
                                "odei nchar(2)," & _
                                "dni nchar(8) NOT NULL, " & _
                                "apePaterno nvarchar(40)," & _
                                "apeMaterno nvarchar(40)," & _
                                "nombre nchar(40)," & _
                                "fechaNac nchar(10)," & _
                                "lugarNac nvarchar(50)," & _
                                "estadoCivil nchar(1)," & _
                                "cargo nchar(2)," & _
                                "nroHijos nvarchar(2)," & _
                                "rangoEdad1 nvarchar(2)," & _
                                "rangoEdad2 nvarchar(2)," & _
                                "rangoEdad3 nvarchar(2)," & _
                                "rangoEdad4 nvarchar(2)," & _
                                "rangoEdad5 nvarchar(2)," & _
                                "rangoEdad6 nvarchar(2)," & _
                                "direccion nvarchar(100)," & _
                                "prov nchar(2)," & _
                                "dist nchar(2)," & _
                                "profesion nvarchar(50)," & _
                                "telefono nvarchar(50)," & _
                                "celular nvarchar(50)," & _
                                "email nvarchar(50)," & _
                                "grupo_sanguineo nvarchar(10)," & _
                                "peso nvarchar(10)," & _
                                "estatura nvarchar(10)," & _
                                "lengua_materna nchar(1)," & _
                                "otra_lengua_materna nvarchar(50)," & _
                                "chaleco nvarchar(50)," & _
                                "polo nvarchar(50)," & _
                                "tipo_nivel nchar(1)," & _
                                "nivel_academico nchar(1)," & _
                                "carrera nvarchar(100)," & _
                                "centro_estudios nvarchar(100)," & _
                                "estudios_anho_inicio nchar(4)," & _
                                "estudios_anho_fin nchar(4)," & _
                                "fecha_titulacion nchar(10)," & _
                                "estudios_computacion nvarchar(1)," & _
                                "curso1 nvarchar(100)," & _
                                "anho1 nchar(4)," & _
                                "meses1 nvarchar(2)," & _
                                "curso2 nvarchar(100)," & _
                                "anho2 nchar(4)," & _
                                "meses2 nvarchar(2)," & _
                                "curso3 nvarchar(100)," & _
                                "anho3 nchar(4)," & _
                                "meses3 nvarchar(2)," & _
                                "curso4 nvarchar(100)," & _
                                "anho4 nchar(4)," & _
                                "meses4 nvarchar(2)," & _
                                "curso5 nvarchar(100)," & _
                                "anho5 nchar(4)," & _
                                "meses5 nvarchar(2)," & _
                                "curso6 nvarchar(100)," & _
                                "anho6 nchar(4)," & _
                                "meses6 nvarchar(2)," & _
                                "experiencia_anhos nchar(4)," & _
                                "experiencia_meses nvarchar(2)," & _
                                "otra_experiencia nvarchar(1)," & _
                                "otra_experiencia_anhos nchar(4)," & _
                                "otra_experiencia_meses nvarchar(2)," & _
                                "seguro nchar(1)," & _
                                "salud_presenta_problemas nchar(1)," & _
                                "salud_problemas nvarchar(10)," & _
                                "practica_deporte nvarchar(1)," & _
                                "deporte nvarchar(100));"
    '"CONSTRAINT pk_Personal PRIMARY KEY (dni)" & _
    '                               ");"

    Sub inicializar(ByVal codigo As String)
        archivoSDF = "data/personal" & codigo & ".sdf"

        Dim existe As Boolean = System.IO.File.Exists(archivoSDF)

        If Not con Is Nothing Then
            con.Dispose()
        End If

        con = SqlCEUtil.getConnection(archivoSDF, False, clave)

        If Not existe Then
            Try
                Dim cmd As New SqlCeCommand()
                cmd.Connection = con
                con.Open()

                cmd.CommandText = sqlCreate
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                con.Close()
            End Try
        End If

    End Sub

    Public Sub guardar(ByVal r As Registro)
        Try
            Dim cmd As New SqlCeCommand()
            cmd.Connection = con
            con.Open()

            Dim s As New SentenciaSql("Personal", r)

            cmd.CommandText = "select * from Personal"
            Dim dr As SqlCeDataReader = cmd.ExecuteReader()
            If dr.Read() Then
                cmd.CommandText = s.getUpdateSql("where dni='" & dr("dni").ToString() & "'")
            Else
                cmd.CommandText = s.getInsertSql()
            End If

            cmd.ExecuteNonQuery()

            'Catch ex As Exception
            '    MsgBox(ex.ToString)
        Finally
            con.Close()
        End Try
    End Sub

    Public Function findPrimerRegistro() As Registro
        Return findPrimerRegistro(con)
    End Function
    Private Function findPrimerRegistro(ByVal con As SqlCeConnection) As Registro
        Dim r As New Registro

        Dim cmd As New SqlCeCommand()
        cmd.Connection = con

        Try
            con.Open()

            cmd.CommandText = "select * from Personal"
            Dim dr As SqlCeDataReader = cmd.ExecuteReader()

            If dr.Read Then
                For i = 0 To dr.FieldCount - 1
                    If IsDBNull(dr(i)) Then
                        r.agregar(dr.GetName(i), "")
                    Else
                        r.agregar(dr.GetName(i), dr(i).ToString())
                    End If

                Next
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con.Close()
        End Try

        Return r
    End Function



    'Public Function findRegistrosEvaluacionCurso() As List(Of Registro)
    'End Function

    Public Sub consolidar(ByRef filesSdf As String(), ByVal fileMdb As String)
        'Dim con_mdb = MdbUtil.getConnection(Application.StartupPath & "/data/Personal.mdb", True, clave)
        Dim con_mdb = MdbUtil.getConnection(fileMdb, True, "")

        Dim cmd As New OleDbCommand()
        cmd.Connection = con_mdb
        Try
            con_mdb.Open()
            cmd.CommandText = sqlCreate
            cmd.ExecuteNonQuery()

            Dim sb As New StringBuilder
            For Each file In filesSdf
                Dim con_sdf_origen = SqlCEUtil.getConnection(file, False, clave)
                Dim r As Registro = findPrimerRegistro(con_sdf_origen)
                Dim s As New SentenciaSql("Personal", r)

                If r.getValores().Count > 0 Then
                    cmd.CommandText = s.getInsertSql()
                    'MsgBox(cmd.CommandText)
                    cmd.ExecuteNonQuery()
                Else
                    sb.AppendLine(file)
                End If
            Next

            MsgBox(filesSdf.Length & " archivos procesados")

            If sb.Length > 0 Then
                Dim log As String = fileMdb & "_log.txt"
                MsgBox("Algunos archivos no tenian datos. Ver " & log)
                Dim s As String = "Archivos sin datos: " & sb.ToString()
                ArchivoUtil.escribirArchivo(log, s)
            End If



        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            con_mdb.Close()
        End Try
    End Sub


End Class
